iT邦幫忙

2021 iThome 鐵人賽

DAY 21
1
Modern Web

網站一條龍 - 從架站到前端系列 第 21

[Day21] 在 .NET 使用 Dapper 操作 MySQL

  • 分享至 

  • xImage
  •  

現在我們的資料庫已經就緒了,我們趕快來透過 .NET Web API 操作資料庫吧!

現在寫 .NET 操作資料庫通常都會用 ORM(Object Relation Map)套件幫我們對應我們的 C# 物件 與資料庫之間的關係。目前 .NET 生態中最流行大概是以下兩個:

  • Entity Framework
    微軟力推的強大 ORM,非常的完整功能強大,搭配 LINQ 語法操作資料庫,只要安裝不同的提供器(provider),就能用同一份 C# 程式操作不同的資料庫。
  • Dapper
    輕量級的 ORM,必須要自己定義 C# class、管理連線、自己寫 SQL,但是套件非常迷你而且效能非常好,很適合已經學會目標資料庫語法的開發者。

ORM 的選擇其實與團隊還有「信仰」比較有關,筆者自己使用的感覺是:EF 真的很方便,Dapper 真的很快。本系列因為介紹過 MySQL 的語法,所以會使用 Dapper 來示範,偷渡一下推廣這個輕巧、高效能的 ORM。

參數化查詢

在實際使用 Dapper 之前,我們先來聊聊參數化查詢。參數化查詢簡單來說就是不要直接用字串寫上 SQL 指令所需要的值,而是在指令中給一個代號,接著再讓我們程式中的物件,根據代號把查詢所需要的值帶入指令的代號中。例如

var queryParams = new { UserId = 1 };
var targetUser = dbConnection.Query(
    "SELECT * FROM `user` WHERE user_id = @UserId",
    queryParams
);

上面的程式碼中,第一行宣告了一個物件,裡面只有一個值為 1 的 UserId。第二行執行 SQL 指令,嘗試從 user 資料表取出 user_id = 1 的資料,這個查詢的字串中我們並沒有直接給值,而是給了一個代號 @UserId,待會真正要查詢的時候,我們才會從 Query() 方法的第二個參數 queryParams 裡面,找出名字為 UserId 的屬性,並把值取出來,最後送到資料庫,由資料庫幫我們把值帶入 SQL 指令並執行。

參數化查詢最主要的好處是可以有效杜絕 SQL 注入(injection)攻擊,SQL 注入簡單來說就是在給值的時候,偷偷藏了可以執行的 SQL 指令在裡面,這樣資料庫在值型我們的查詢的時候,就有可能執行被偷藏在裡面的有害指令,造成我們的資料外洩或損毀。

安裝 Dapper

拖了這麼久,終於要開始使用 Dapper 的章節了!首先,我們需要安裝相關的套件

  1. Dapper
  2. MySQL Connector 或者 MySQL Data

MySQL Connect 與 MySQL Data 用法基本上都一樣,只是 MySQL Connector 是社群在維護,授權是 MIT;MySQL Data 是 Oracle 官方在維護,授權是 GPLv2

首先對我們的專案點右鍵 -> 管理 NuGet 套件,然後在瀏覽那邊搜尋這兩個套件並安裝。基本上直接裝最新版就好不必刻意選版本,有很舊的專案再注意一下相依版本就好。

https://ithelp.ithome.com.tw/upload/images/20210921/20140664Ze7IZl8npL.png
https://ithelp.ithome.com.tw/upload/images/20210921/20140664zuvTjr0Axo.png

Dapper 基礎用法

裝完之後就可以開始來用 Dapper 了,Dapper 的基本用法包含四個步驟

  1. 定義一個用來接資料的 Model 類別
    Dapper 支援泛型,只要把指定的型別告訴 Dapper,而且類別的屬性與 MySQL 的欄位有對上,Dapper 就會自動幫我們把取回來的資料放進 Model 類別。開發時期資料還不固定可以暫時用 dynamic,但是固定下來之後建議還是宣告一個 Model Class 來儲存,因為 dynamic 型別比較容易出錯、不容易維護。

MySQL 與 .NET 的命名慣例不同,MySQL 大多使用小寫英文 + 下底線,.NET 大多使用駝峰式命名,大多數的狀況無法直接對應上,這時候只要使用 AS 語法改變選取結果的名稱,就能輕鬆地對上。例如
SELECT user_naem AS UserName FROM `user` WHERE user_id = 1;

  1. 開啟一個 MySQL 連線
    var _db = new MySqlConnection("連線資訊"); 就能開啟連線。比較需要注意的是,最好用注入組態設定的方法取得連線資訊,以及用 using 語法自動幫我們關閉連線,以免忘記關閉造成連線被占用。

  2. 執行 SQL 指令
    Dapper 有多種執行 SQL 指令的語法,以下對常用的幾個語法做說明

    • 查詢多筆資料
      用 Query<> 泛型方法,執行完 SQL 查詢完成後,Dapper 會自己幫我們把資料對應到 Model 類別
    var results = _db.Query<Model類別>(“查詢多筆資料的 SQL 指令”).ToList();
    
    • 查詢一個純量
      使用 ExecuteScalar<> 泛型方法,查詢完成後 Dapper 會自動把純量轉型。
    var result = _db. ExecuteScalar<資料型態>(“查詢純量的 SQL 指令”);
    
    • 執行 SP 並取得回傳值(SP 最後 SELECT 的資料)
      同樣使用 Query<> 泛型方法,但需要指定 commandType 為 StoredProcedure
    var spParams = new { 參數名稱1: 參數值1, 參數名稱2: 參數值2, …};
    var results = _db.Query<Model類別>("SP名稱", spParams, commandType: CommandType.StoredProcedure);
    
    • 執行 SP 但不接資料
      使用 Execute() 方法,一樣要指定 commandType 為 StoredProcedure
    _db. Execute("SP名稱", spParams, commandType: CommandType.StoredProcedure);
    
    • 執行 SP 並取得 OUT 參數的值
      使用 Execute() 方法搭配 DynamicParameters 類別儲存參數,OUT 參數需要額外再指定 direction 為 Output,一樣要指定 commandType 為 SP。執行完畢後,可以從 DynamicParameters 透過 Get 泛型方法,把結果的值取出。
    var spParams = new DynamicParameters();
    spParams.Add("IN參數名稱1", IN參數值1);
    spParams.Add("IN參數名稱2", IN參數值2);
    ...
    spParams.Add("OUT參數名稱1", OUT參數值2, direction: ParameterDirection.Output);
    spParams.Add("OUT參數名稱2", OUT參數值2, direction: ParameterDirection.Output);
    ...
    
    _db.Execute("SP名稱", spPrams, commandType: CommandType.StoredProcedure);
    
    var outParam1 = spParams.Get<資料型態1>("OUT參數名稱1");
    var outParam2 = spParams.Get<資料型態2>("OUT參數名稱2");
    ...
    

    4 關閉連線
    如果前面開啟連線沒有使用 using 關鍵字,務必記得手動關閉連線
    db?.Close()

用 Dapper 做一個 User Service

請各位回味一下本系列關於依賴注入的文章,我們現在要來建立另一個實作 IUserCRUD 介面的 Service,用 MySQL 管理使用者資料。

首先,因應資料庫的變更修改我們的 User 類別

public class User
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    public bool Verified { get; set; } // 加入這個屬性
}

再來,新增 MySQL 需要的連線資訊到 appsettings.json 與 appsettings.Development.json

// appsettings.json 
// 其他不變
"MySqlOptions": {
    "ConnectionString": "Server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;" // 要上版控前記得挖掉密碼
}
// appsettings.Development.json 
// 其他不變
"MySqlOptions": {
    "ConnectionString": "Server=localhost;Database=world;Uid=root;Pwd=root;" // 本機用的不會上版控,可以放心打密碼
}

接著,宣告一個 MySqlOption 類別,用來儲存 appsettings 裡的設定值,並在 Startup.cs 註冊 MySQL 需要用到的 Option

public class MySqlOptions
{
    public string ConnectionString { get; set; }
}
services.Configure<MySqlOptions>(
    Configuration.GetSection("MySqlOptions"));

主角終於登場了!宣告一個 UserServiceWithMySQL 並實作 IUserCRUD 介面,透過 Dapper 操作 MySQL

public class UserServiceWithMySQL : IUserCRUD
{
    private readonly IOptions<MySqlOptions> _options;

    public UserServiceWithMySQL(IOptions<MySqlOptions> options)
    {
        _options = options;
    }

    public void CreateUser(User model)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            bool emailUsed = db.ExecuteScalar<bool>(
                @"SELECT EXISTS 
                    (SELECT user_id FROM `user` WHERE email = @Email)",
                model);

            if (!emailUsed)
            {
                db.Execute(
                    @"INSERT INTO `user` (user_name, email, verified)
                      VALUES (@UserName, @Email, 0)",
                    model);
            }
        }
    }

    public void DeleteUser(int id)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            db.Execute(
                @"DELETE FROM `user`
                  WHERE user_id = @UserId",
                new { UserId = id});
        }
    }

    public List<User> GetAllUsers()
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            var result = db.Query<User>(
                @"SELECT
                    user_id AS UserID,
                    user_name AS UserName,
                    email AS Email,
                    verified AS Verified
                  FROM `user`",
                new {});
            return result.ToList();
        }
    }

    public User GetUserById(int id)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            var result = db.QuerySingleOrDefault<User>(
                @"SELECT
                    user_id AS UserID,
                    user_name AS UserName,
                    email AS Email,
                    verified AS Verified
                  FROM `user`
                  WHERE user_id = @UserId",
                new { UserId = id});
            return result;
        }
    }

    public void UpdateUser(int id, User model)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            bool emailUsed = db.ExecuteScalar<bool>(
                @"SELECT EXISTS 
                    (SELECT user_id FROM `user` WHERE email = @UserId)",
                model);

            if (!emailUsed)
            {
                db.Execute(
                    @"UPDATE `user` 
                      SET
                        user_name = @UserName,
                        email = @Email,
                        verified = @Verified
                      WHERE
                        user_id = @UserId",
                    model);
            }
        }
    }
}

寫完 Service 之後,到 Startup.cs 換掉註冊的 UserService

// 把原本的 UserService 替換成 UserServiceWithMySQL
services.AddScoped<IUserCRUD, UserServiceWithMySQL>();

因為我們的 Controller 依賴 IUserCRUD,而我們新的 service 有實作 IUserCRUD,所以 Controller 完全不用改,F5 執行就直接把使用者的管理換到 MySQL,舒服!


明天,我們將短暫的再次回到雲端,在我們的 VM 上面裝 MySQL,然後調整環境,讓佈署在上面的 API 程式存取同一個 VM 上的 MySQL。


上一篇
[Day20] MySQL 的 Stored Procedure
下一篇
[Day22] 在 GCP 的 VM 上安裝 MySQL
系列文
網站一條龍 - 從架站到前端33
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言